Quiz 7
We'll cover the following
Question # 1#
Consider the following two tables, that are related by the column MovieID which is unique. Additionally, assume there are no duplicate rows in either of the tables.
Can you write a query that emulates a full join between the two tables?
Recall that a full join includes all the rows from the two tables. Furthermore, we are assured that neither of the tables has a duplicate row. We’ll relax this condition in the next question and see how it affects the outcome. For this question, we can UNION the result of the left join of the movie table with the right join of the cinema table to get a full join between the two tables. Note that the UNION operator will include the intersecting part/rows between the two tables only once.
SELECT MovieID, MovieName FROM Movie
LEFT JOIN Cinema
WHERE MovieID = MovieID;
UNION
SELECT MovieID, CinemaName FROM Movie
RIGHT JOIN Cinema
WHERE MovieID = MovieID;
Question # 2#
Consider the following two tables that have duplicate rows. What would be the outcome of the full join?
Table A
| aID |
|---|
| 3 |
| 5 |
| 5 |
| 7 |
Table B
| bID |
|---|
| 1 |
| 5 |
| 8 |
| 8 |
Theoretically, the correct answer is as follows:
| aID | bID |
|---|---|
| 3 | NULL |
| 5 | 5 |
| 5 | 5 |
| 7 | NULL |
| NULL | 1 |
| NULL | 8 |
| NULL | 9 |
Each 5 from Table A matches with the only 5 in Table B and we get two rows of (5, 5) in the resulting table. Also, notice the two 8s from Table B appear in the final result. If we follow the same query pattern for a full join from the previous question, we’ll get the following result:
SELECT * FROM A
LEFT JOIN B
ON aID = bID
UNION
SELECT * FROM A
RIGHT JOIN B
ON aID = bID;
The problem with the above approach is that the UNION operator removes duplicates as part of its behavior. Can we use UNION ALL? Let’s find out
SELECT * FROM A
LEFT JOIN B
ON aID = bID
UNION ALL
SELECT * FROM A
RIGHT JOIN B
ON aID = bID;
The problem with using UNION ALL is that the intersecting portion between the two tables is printed twice. Once when the two 5s from Table A match the single 5 in Table B and once when the single 5 matches the two 5s in Table A. The right solution is to minus this intersecting portion once from the resulting set. One approach can be to take the left join for Table A and then only take those values from the right join for Table B that have a corresponding NULL entry for the aID column, meaning that such rows are only part of Table B. The complete query is shown below:
SELECT * FROM A
LEFT JOIN B
ON aID = bID
UNION ALL
SELECT * FROM A
RIGHT JOIN B
ON aID = bID
WHERE aID IS NULL;
Now, you can observe the practical result matches our theoretical result. The astute reader would realize we can achieve the same result if we used the WHERE clause with the left join and took the entirety of the right join. The query is shown below:
SELECT * FROM A
LEFT JOIN B
ON aID = bID
WHERE bID IS NULL
UNION ALL
SELECT * FROM A
RIGHT JOIN B
ON aID = bID;
Question # 3
Can a column which isn’t unique or the primary key in a parent act as a foreign key in a child table?
A)
True
B)
False
Question # 4
Which of the following is a disadvantage of defining foreign key constraints?
A)
There are no disadvantages.
B)
May slow down application as write operations require verifying foreign key constraints.
C)
Foreign keys can come with an increase in size as MySQL requires an index to be defined on the foreign key.
D)
B and C
Question # 5
Which of the following is true about foreign keys?
A)
An alternative to foreign key constraint is to enforce checks at the application layer and remove foreign key constraints at the database layer.
B)
A table can define a foreign key relationship between its two columns as long as the two columns aren’t the same column.
C)
MyISAM storage engine also supports foreign keys.
D)
The primary purpose of foreign keys is to speed up read queries.
E)
A and B
F)
D and A
